package com.petshop.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.common.constant.BalzLogger;
import com.common.constant.ProjectConstant;
import com.common.jdbc.JDBCConnection;
import com.petshop.model.User;
import com.petshop.model.UserRole;


public class UserProfileDaoImpl extends JDBCConnection implements UserProfileDao{
	
	BalzLogger logger = new BalzLogger();
	
	private static final StringBuilder SQL_GET_USER_PROFILE = new StringBuilder().append(
			" SELECT * FROM balz_user AMU JOIN balz_role AMR  ").append(
			" WHERE (SELECT ROLE_ID FROM balz_user_map_role AUR  " ).append(
			" WHERE AMU.USER_ID = AUR.USER_ID  AND AUR.STATUS = 'Y') = amr.ROLE_ID  " ).append(
			" AND AMU.STATUS = 'Y' AND AMR.STATUS = 'Y' " );
	public List<User> getUserProfileList() {
	
		ResultSet rs = queryBySql(SQL_GET_USER_PROFILE.toString());
		List<User> userProfiles = new ArrayList<User>();
		
		try {
			while (rs.next()) {
				userProfiles.add(mapRow(rs)); // Put the Data Model to UserProfile list
			}
			rs.close();
		} catch (SQLException e) {
	    	logger.debug("SQLException: " + e.getMessage());
	    }
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + SQL_GET_USER_PROFILE.toString());
			logger.debug("result.size(): " + userProfiles.size());
			logger.debug("result>> " + userProfiles);
		}
		
		return userProfiles;
	}
	
	
//	private static final StringBuilder SQL_GET_USER_ROLE = new StringBuilder().append(
//			" SELECT * FROM balz_user AMU JOIN balz_role AMR  ").append(
//			" WHERE (SELECT ROLE_ID FROM balz_user_map_role AUR  " ).append(
//			" WHERE AMU.USER_ID = AUR.USER_ID  AND AUR.STATUS = 'Y') = amr.ROLE_ID  " ).append(
//			" AND AMU.STATUS = 'Y' AND AMR.STATUS = 'Y' " );	
//	public List<UserRole> getUserRole() {
//		
//		ResultSet rs = queryBySql(SQL_GET_USER_ROLE.toString());
//		List<UserProfile> userProfiles = new ArrayList<UserProfile>();
//		
//		try {
//			while (rs.next()) {
//				userProfiles.add(mapRow(rs)); // Put the Data Model to UserProfile list
//			}
//		} catch (SQLException e) {
//	    	logger.debug("SQLException: " + e.getMessage());
//	    }
//		
//		if (logger.isDebugEnabled()) {
//			logger.debug("sql: " + SQL_GET_USER_PROFILE.toString());
//			logger.debug("result.size(): " + userProfiles.size());
//			logger.debug("result>> " + userProfiles);
//		}
//		
//		return userProfiles;
//	}
	
	public Integer checkUserNamePassword(String username, String password) {
		
		Integer isUserRole = ProjectConstant.ROLE_LOCAL_USER;
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT AMU.USER_ID, AMR.ROLE_ID FROM balz_user AMU ");
		sql.append(" JOIN balz_user_map_role AMR WHERE AMU.USER_ID = AMR.USER_ID ");
		sql.append("AND AMU.USERNAME = '"+username+"'");
		sql.append(" AND AMU.PASSWORD =");
		sql.append("'"+password.trim()+"'");
		
		ResultSet rs = null ;
		rs = queryBySql(sql.toString());
		
		if (logger.isDebugEnabled()) {
			logger.debug("sql: " + sql.toString());
			logger.debug("username >> " + username);
			logger.debug("password >> " + password);
		}
		
		if(rs != null){
			try {
				while (rs.next()) {
					
					Integer roleId = Integer.parseInt(rs.getString("ROLE_ID").trim());
					
					logger.debug("ROLE_ID >> " + roleId);
					
					if(ProjectConstant.ROLE_ADMINISTRATOR == roleId ){
						isUserRole = ProjectConstant.ROLE_ADMINISTRATOR ;
					}else{
						isUserRole = ProjectConstant.ROLE_LOGIN_USER ;
					}
				}
			} catch (SQLException e) {
		    	logger.debug("SQLException: " + e.getMessage());
		    }
		}
		
		logger.debug("ROLE_ID >> : " + isUserRole);
		
		return isUserRole;
	}
	
	// ---------------------------------------------------------------------------------------------------- RowMapper
	public User mapRow(ResultSet rs) throws SQLException {
		
		User model = new User();
		
		model.setUserId(rs.getString("USER_ID"));
		model.setUserName(rs.getString("USERNAME"));
		model.setPassword(rs.getString("PASSWORD"));
		model.setForcePassChangeFlag(rs.getString("FORCE_PWD_CHNG_FLAG"));
		model.setLastLogin(rs.getTimestamp("LAST_LOGIN"));
		model.setStatus(rs.getString("STATUS"));
		model.setCreatedBy(rs.getString("CREATED_BY"));
		model.setCreatedDate(rs.getTimestamp("CREATED_DATE"));
		model.setUpdatedBy(rs.getString("UPDATED_BY"));
		model.setUpdatedDate(rs.getTimestamp("UPDATED_DATE"));
		
		UserRole role = new UserRole();
		
		// Role ---------
		role.setRoleId(rs.getString("ROLE_ID"));
		role.setRoleName(rs.getString("ROLE_NAME"));
		role.setRoleDesc(rs.getString("ROLE_DESC"));
		model.setUserRole(role);
		
		return model;
	}
	
	
}
